package com.winton.dao.impl;

import java.util.List;

import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import com.winton.Z.Page;
import com.winton.dao.UserDao;
import com.winton.model.S_USER;
import com.winton.model.S_USERITEM;

@Repository
public class UserDaoImpl extends DaoBase implements UserDao{

	
	@Override
	public boolean insertUser(S_USER user) {
		
		StringBuilder sBuilder=new StringBuilder();
		sBuilder.append("insert into S_USER(id,username,loginid,password,systime,deleted)");
		sBuilder.append(" values (seq_userid.nextval,?,?,?,SYSDATE,0)");
		
		Object[] objArr = user.getInsertFields();
		int count = jdbcTemplate.update(sBuilder.toString(), objArr);
		
		return (count > 0);
	}

	@Override
	public boolean updateUser(S_USER user) {
		
		StringBuilder sBuilder=new StringBuilder();
		sBuilder.append("update S_USER set username= ?,loginid= ?,password= ?,systime= sysdate where id= ? ");
				
		Object[] objArr = user.getUpdateFields();
		int count = jdbcTemplate.update(sBuilder.toString(), objArr);
		
		return (count > 0);
	}

	@Override
	public int selectUserListCount(String strWhere, Object[] objArr) {
		StringBuilder sBuilder=new StringBuilder();						
		sBuilder.append("select count(1) from S_USER");
		sBuilder.append(strWhere.length() > 0 ? " where 1=1 " + strWhere : "");
		Integer count = jdbcTemplate.queryForObject(sBuilder.toString(), Integer.class, objArr);
		return count;
	}

	@Override
	public Object queryUserList(Page page, String strWhere, Object[] objArr) {
		RowMapper<S_USER> row = new BeanPropertyRowMapper<>(S_USER.class);
		StringBuilder sBuilder=new StringBuilder();
		sBuilder.append("select t1.* from (");
		sBuilder.append(" select ROWNUM as r,t.* from (");
		sBuilder.append("  select * from S_USER");
		sBuilder.append(strWhere.length() > 0 ? " where 1=1  " + strWhere : "");
		sBuilder.append("  order by id desc");
		sBuilder.append(" ) t");
		sBuilder.append(" where ROWNUM<=" + (page.getStart() + page.getRow()));
		sBuilder.append(") t1");
		sBuilder.append(" where t1.r>" + page.getStart());
		List<S_USER> list = jdbcTemplate.query(sBuilder.toString(), row, objArr);
		return list;
	}

	@Override
	public boolean updateUserDeleted(S_USER user) {
		
		StringBuilder sBuilder=new StringBuilder();
		sBuilder.append("update S_USER set deleted= ? where id= ? ");
		int deleted	= (user.getDeleted()==1) ? 0 : 1;
		int count = jdbcTemplate.update(sBuilder.toString(), new Object[] {deleted,user.getId()});
		
		return (count > 0);
		
	}

	@Override
	public int deleteUserByUserId(int id) {
		StringBuilder sBuilder=new StringBuilder();
		sBuilder.append("delete from S_USER  where id=? ");
		
		Object[] objArr = new Object[]{id};
		int count = jdbcTemplate.update(sBuilder.toString(), objArr);
		
		return count;
	}

	@Override
	public S_USER findUserByUserId(int id) {
		RowMapper<S_USER> row = new BeanPropertyRowMapper<>(S_USER.class);
		String sql = "select * from S_USER where id=? ";
		S_USER model = jdbcTemplate.queryForObject(sql, row, new Object[]{id});
		
		return model;
	}

	@Override
	public List<S_USER> loginidVerify(String loginid) {
		RowMapper<S_USER> row = new BeanPropertyRowMapper<>(S_USER.class);
		String sql = "select * from S_USER where loginid=? ";
		List<S_USER> model = jdbcTemplate.query(sql, row, new Object[]{loginid});
		
		return model;
	}

	@Override
	public S_USER userLogin(S_USER user) {
		RowMapper<S_USER> row = new BeanPropertyRowMapper<>(S_USER.class);
		String sql = "select * from S_USER where loginid=? and password=? and deleted=0 ";
		List<S_USER> model = jdbcTemplate.query(sql, row, new Object[]{user.getLoginid(),user.getPassword()});
		S_USER u = null;
		  if (model != null && model.size() > 0) {
			  u = model.get(0);
		  }
		return u;		
	}

	@Override
	public List<S_USERITEM> queryUserItemByUserId(int userid) {
		RowMapper<S_USERITEM> row = new BeanPropertyRowMapper<>(S_USERITEM.class);
		String sql = "select * from S_USERITEM where userid=? ";
		List<S_USERITEM> model = jdbcTemplate.query(sql, row, new Object[]{userid});
		
		return model;
	}

	@Override
	public int addUserItem(int userid, String strid) {
		StringBuilder sBuilder=new StringBuilder();
		sBuilder.append("insert into S_USERITEM(userid,itemid)");
		sBuilder.append(" with a as ( select ? as str from dual ) ");
		sBuilder.append(" select ? ,regexp_substr(str,'[^,]+',1,rownum) str from a ");
		sBuilder.append(" connect by rownum <= length(regexp_replace(str,'[^,]+'))+1 ");
							
		int count = jdbcTemplate.update(sBuilder.toString(),new Object[] {strid,userid});
		
		return count;
	}

	@Override
	public int deleteUserItem(int userid) {
		StringBuilder sBuilder=new StringBuilder();
		sBuilder.append("delete from S_USERITEM  where userid=?  ");
				
		int count = jdbcTemplate.update(sBuilder.toString(), new Object[] {userid});
		
		return count;
	}

	@Override
	public int updatePassword(int id, String newpassword) {
		StringBuilder sBuilder=new StringBuilder();
		sBuilder.append("update S_USER set password = ? where id= ? ");
		
		int count = jdbcTemplate.update(sBuilder.toString(), new Object[] {newpassword,id});
		
		return count;
	}

}
